package com.ldr;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.HashMap;
import java.util.Map;

/**
 * @author：ldr
 * @date：2025/1/14 16:31
 * @description：用于将公司的Google翻译文档Excel转strings.xml 对应会生成多个语言的strings.xml
 */
public class GoogleExcelToStringXml {


    public static void convertExcelToStringsXml(String excelFilePath, String outputDirPath) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(excelFilePath);
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 获取标题行以定位列索引
        Row headerRow = sheet.getRow(1);
        if (headerRow == null) {
            throw new IllegalArgumentException("Excel 文件无标题行！_name English 等标题栏");
        }

        int nameColIndex = -1;
        //记录语言列的索引
        Map<String, Integer> languageColumns = new HashMap<>();

        for (Cell cell : headerRow) {
            String header = getCellStringValue(cell).trim();
            if (header.isEmpty()) continue;
            if ("_name".equalsIgnoreCase(header)) {
                nameColIndex = cell.getColumnIndex();
                System.out.println("cell.getColumnIndex()"+nameColIndex);
            } else {
                languageColumns.put(header, cell.getColumnIndex());
                System.out.println("cell.getColumnIndex()"+header);
            }
        }

        if (nameColIndex == -1 || languageColumns.isEmpty()) {
            throw new IllegalArgumentException("无法找到 '_name' 列或任何语言列！");
        }


        // 准备输出目录
        File outputDirectory = new File(outputDirPath);
        if (!outputDirectory.exists()) {
            if (!outputDirectory.mkdirs()) {
                throw new IOException("无法创建输出目录: " + outputDirPath);
            }
        }

        // 遍历语言列并导出每个语言的 XML
        for (Map.Entry<String, Integer> entry : languageColumns.entrySet()) {
            String language = entry.getKey();
            int languageColIndex = entry.getValue();

            File outputFile = new File(outputDirectory, language + "-strings.xml");
            try (BufferedWriter writer = new BufferedWriter(new FileWriter(outputFile))) {
                writer.write("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
                writer.write("<resources>\n");

                // 遍历数据行
                for (int i = 2; i <= sheet.getLastRowNum(); i++) { // 从第 3 行开始（因为google上的翻译就是从第三行开始的）
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    Cell nameCell = row.getCell(nameColIndex);
                    Cell valueCell = row.getCell(languageColIndex);

                    if (nameCell == null || valueCell == null) continue;

                    String name = getCellStringValue(nameCell).trim();
                    String value = getCellStringValue(valueCell).trim();

                    if (name.isEmpty() || value.isEmpty()) continue;

                    // 转义特殊字符
                    value = escapeXml(value);

                    writer.write(String.format("    <string name=\"%s\">%s</string>\n", name, value));
                }

                writer.write("</resources>");
            }
        }

        workbook.close();
        fileInputStream.close();
    }

    private static String escapeXml(String value) {
        if (value == null) return "";
        return value.replace("&", "&amp;")
                .replace("<", "&lt;")
                .replace(">", "&gt;")
                .replace("\"", "&quot;")
                .replace("'", "&#39;");
    }

    private static String getCellStringValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula(); // 或者尝试计算公式值
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}
